SQL Server 效能調教

TLDR

使用 SSMS 的「實際評估計畫」可分析已執行查詢的效能瓶頸，而「顯示估計執行計畫」則適合預判未執行查詢的效能。
建立索引時應謹慎，過多索引會降低寫入效能，建議優先整合現有索引。
複合索引遵循「左前綴規則」，查詢條件必須包含索引最左側欄位才能有效利用。
處理鎖定衝突可視需求選用 NOLOCK（忽略鎖定）、NOWAIT（不等待立即報錯）或 READPAST（跳過鎖定資料）。
Parameter Sniffing 常見於參數化查詢，可透過 Query Store 強制計畫、OPTIMIZE FOR 提示、RECOMPILE 或清除快取來解決。

使用 SSMS 執行計畫工具分析和最佳化查詢

在調整 SQL Server 效能時，SSMS 提供的執行計畫工具是核心手段。

包括實際評估計畫：顯示查詢實際執行時的統計資訊（如耗時、讀取量），適用於調整已執行過的查詢。
顯示估計執行計畫：根據統計資料預估執行路徑，適用於分析未執行的查詢。

補上遺漏的索引

當執行計畫顯示存在「遺漏索引」建議時，可透過右鍵點擊執行計畫並選擇「遺漏索引詳細資訊」來產生建立語法。

WARNING

請勿單純根據遺漏索引建議建立索引，許多索引是可以整合的。過多的索引會導致寫入效能下降。

若需從 sys.dm_db_missing_index_groups 等檢視中找出遺漏索引，可使用以下語法：

sql

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1),
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' +
        CONVERT (varchar, mig.index_group_handle) + '_' +
        CONVERT (varchar, mid.index_handle) + ' ON ' +
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') +
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' +
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;

SQL Server 索引簡介

索引基於 B-Tree 結構，主要分為叢集索引（Clustered Index）與非叢集索引（Non-Clustered Index）。

叢集索引：決定資料物理排序，每個資料表僅限一個。適合連續性高的資料（如流水號），不建議使用 GUID。
非叢集索引：在叢集索引之上建立的二級索引，可包含多個。
INCLUDE 用途：在非叢集索引中包含非鍵欄位，可減少從資料頁面讀取資料的次數，提升查詢效能。
複合索引左前綴規則：查詢條件必須與索引最左側連續欄位匹配，索引才能發揮效能。若查詢條件不符合此規則，SQL Server 最佳化器通常不會使用該索引。

查詢遇到鎖定資料的處理方式

當發生鎖定衝突（Blocking）時，可依據業務需求選擇以下處理方式：

NoLock：忽略鎖定直接讀取，可能讀取到未提交的資料（Dirty Read）。
sql
```
SELECT * FROM TableName WITH (NOLOCK) WHERE Condition;
```
1

NoWait：不等待鎖定釋放，立即返回錯誤。

sql

SELECT * FROM TableName WITH (NOWAIT) WHERE Condition;

ReadPast：跳過已被鎖定的資料列，只讀取可用的資料。
sql
```
SELECT * FROM TableName WITH (READPAST) WHERE Condition;
```
1

Parameter Sniffing

Parameter Sniffing 指資料庫根據首次執行的參數值產生執行計畫，若該參數值具備極端特性，可能導致後續使用不同參數時效能下降。

效能問題辨識

若參數化查詢執行緩慢，但改為非參數化查詢後效能恢復，通常即為 Parameter Sniffing 導致。可透過以下腳本找出執行時間變異極大的查詢：

sql

SELECT TOP 20
    t.text AS [SQL Text],
    st.execution_count,
    [Max Elapsed (ms)] = st.max_elapsed_time / 1000,
    [Avg Elapsed (ms)] = (st.total_elapsed_time / st.execution_count) / 1000,
    [Max/Avg Ratio] = CAST(st.max_elapsed_time * 1.0 / NULLIF(st.total_elapsed_time / st.execution_count, 0) AS DECIMAL(10,2)),
    st.plan_handle,
    st.last_execution_time
FROM sys.dm_exec_query_stats AS st
CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) AS t
WHERE t.dbid = DB_ID('{YourDatabaseName}')
  AND st.execution_count > 50
ORDER BY [Max/Avg Ratio] DESC;

解決方法

Query Store：使用「強制計劃 (Force Plan)」功能固定穩定的執行計畫。
OPTION (OPTIMIZE FOR)：強制優化器針對特定值或平均值（UNKNOWN）產生計畫。
OPTION (RECOMPILE)：每次執行皆重新編譯，適用於變異極大的報表查詢，但不適合高併發環境。
清除執行計畫快取：使用 DBCC FREEPROCCACHE 清除特定或全部計畫，強制重新編譯。

異動歷程

2023-03-15
- 初版文件建立。
2026-01-01
- 修正 Parameter Sniffing 檢測腳本中判定邏輯與預期相反的錯誤。
- 增加 Parameter Sniffing 的其他解決方案。

SQL Server 效能調教 ​

TLDR ​

使用 SSMS 執行計畫工具分析和最佳化查詢 ​

補上遺漏的索引 ​

SQL Server 索引簡介 ​

查詢遇到鎖定資料的處理方式 ​

Parameter Sniffing ​

效能問題辨識 ​

解決方法 ​

異動歷程 ​